\documentclass[a4paper]{article}
\usepackage{graphicx}
\usepackage{epsfig}
\usepackage{subfig}
\usepackage[utf8]{inputenc}
\usepackage{amsmath}
\usepackage{listings}
\usepackage[usenames,dvipsnames,svgnames,table]{xcolor}
\usepackage[table]{xcolor}
\usepackage{array}
\usepackage[parfill]{parskip}
 

\title{\scshape{CS2102 Database Systems \\Project Group 31 \\ \vspace*{5mm} Stewie's Market \\an online trading system}}
\author{Mikael Rosell, A0103950\\ 
Alexander Qvick Faxå, A0103863\\
Lam Yuen Ting Kristy, A0103863\\
Robert Eric Schneidman, A0103863\\ 
Ho Man Sing, A0106915\\ 
Zeno Hao, A0104983\\ }
\begin{document}


\texttt{SELECT s.name, s.matric FROM student s WHERE s.projectgroup = 31}\\\\
\begin{tabular}{|p{0.5\textwidth}|p{0.5\textwidth}|}
  \hline
  \cellcolor{gray!25}\textbf{name} &\cellcolor{gray!25} \textbf{matric} \\ \hline
  Mikael Rosell & A0103950\\  \hline
Alexander Qvick Faxå & A0103863\\ \hline
Lam Yuen Ting Kristy & A0103863\\ \hline
Robert Eric Schneidman & A0103863\\ \hline 
Ho Man Sing & A0106915\\ \hline 
Zeno Hao & A0104983\\ \hline
\end{tabular}
\lstset{
language=SQL,                             % Code langugage
basicstyle=\ttfamily,                   % Code font, Examples: \footnotesize, \ttfamily
% keywordstyle=\color{blue},        % Keywords font ('*' = uppercase)
% commentstyle=\color{OliveGreen},              % Comments font
% numbers=left,                           % Line nums position
% numberstyle=\small,                      % Line-numbers fonts
% stepnumber=none,                           % Step between two line-numbers
% numbersep=5pt,                          % How far are line-numbers from code
% backgroundcolor=blue!25,					% Choose background color
frame=none,                             % A frame around the code
tabsize=2,                              % Default tab size
captionpos=b,                           % Caption-position = bottom
breaklines=true,                        % Automatic line breaking?
breakatwhitespace=false,                % Automatic breaks only at whitespace?
showspaces=false,                       % Dont make spaces visible
showtabs=false,                         % Dont make tabls visible
columns=flexible,                       % Column format
morekeywords={__global__, __device__},  % CUDA specific keywords
}

% First page. Title, authors and date
\setcounter{page}{1}
\pagenumbering{roman}
\maketitle
\date

\newpage

% Table of contents
\tableofcontents
\newpage


%********************Start*****************************
\setcounter{page}{1}
\pagenumbering{arabic}
\setcounter{page}{1}
\section{Introduction} % Short introduction and background to the project
% As a part of the module CS2102 Database System at National University of Singapore (NUS), students are required to build a small SQL application. 
% This project contributes to 11\% of the final marks and is conducted by a group of six student. 

% The goal of the NUS BuySellTrade project is to build an application where NUS students can buy, sell and trade items with each other. 
% The idea allows students to profit by selling used items as well as by acquiring second hand items at lower rates.

% The platforms shall allow students to create an account, log in to the service, upload items for sale and search for items in the database. 
% The system shall also provide a messaging service to handle the contact between buyer and seller.
Stewie's Market is an online trading system for NUS students to buy, sell and trade items. The objective is to let students sell used items and acquire second hand items at lower rates.

Students have to register an account with their matriculation number and log in to the system. They can:
\begin{itemize}
\item add an advertisement
\item delete their own advertisements
\item search for advertisements
\item send a message to the owner of an advertisement
\item read and respond to incoming messages
\end{itemize}

\section{About the system} % Indication of Web server, server page language, and DBMS used
The system is implemented as a web page communicating with the CS2102 NUS SOC Oracle server. The web page uses php as its backend language and OCI to communicate between php and Oracle.
Personal Computers are connected to virtual private network of NUS School of Computing with the following implementation:

Web Server: Apache (Mac) / Wamp (Windows)\\
Server page language: PHP\\
Database management system: Oracle\\
Implemented with zone: No


\section{Entity-Relationship diagram}
Figure~\ref{ER} shows the Entity-Relationship diagram for Stewie's Market.

\begin{figure}[!ht]
\begin{center}
\includegraphics[width=1\textwidth]{ER.png}
\caption{Entity-Relationship diagram for Stewie's Market. \label{ER}}
\end{center}
\end{figure}
\newpage

\subsection{Description of ER diagram}
\begin{tabular}{|p{1\textwidth}|}
  \hline
  \cellcolor{gray!25}\textbf{student} \\
  \textbf{matric} is the primary key of \textbf{student}.
A student can optionally add advertisements.
A student can also choose to send messages to the seller/buyer/trader (receiver) of an advertisement to request more information about the content of the ad.\\
\hline
\end{tabular}

\begin{tabular}{|p{1\textwidth}|}
  \hline
  \cellcolor{gray!25}\textbf{ad} \\
  \textbf{matric} is the primary key of \textbf{student}.
Each advertisement is uniquely identified by the student that created it and the time it was posted to the market. Hence, \textbf{ad} is a weak entity.\\
\hline
\end{tabular}

\begin{tabular}{|p{1\textwidth}|}
  \hline
  \cellcolor{gray!25}\textbf{message} \\
  \textbf{matric} is the primary key of \textbf{student}.
\textbf{sender}, \textbf{receiver}, \textbf{message\_time} identifies each individual message in the message table.
Each message has one single sender and one single receiver.\\
\hline
\end{tabular}

% \subsection{Integrity constraints}
% Integrity constraints are used to interpret the rules of the application in the database tables.
% Following integrity constraints are used to handle the behavior of the system: 
% \texttt{PRIMARY KEYs:} 
% \texttt{student: matric}
% \texttt{FOREIGN KEYs:} 
% \texttt{CHECK constraints:} 
% \texttt{request = \{'buy', 'sell', 'trade'\}}
% \texttt{category = \{'clothes', 'electronics', 'furniture', 'stationary', 'kitchenware', 'books', 'others'\} }
% \texttt{condition = \{'as new', 'good', 'worn out'\} }


\section{Relational schema (in SQL DDL code)} 
Three tables \textbf{student}, \textbf{ad} and \textbf{message} are part of the database.

\begin{tabular}{|p{1\textwidth}|}
  \hline
  \cellcolor{gray!25} \textbf{student} stores the information of all the users.
Every student has a unique matriculation number, \textbf{matric},  which is the primary key of \textbf{student}. \\
 	\begin{lstlisting}
CREATE TABLE student(
	name VARCHAR(128) NOT NULL,
	email VARCHAR(128) NOT NULL,
	contact_no VARCHAR(12) NOT NULL,
	password VARCHAR(32) NOT NULL,
	matric VARCHAR(12) PRIMARY KEY);	\end{lstlisting} \\
\hline
\end{tabular}

\begin{tabular}{|p{1\textwidth}|}
  \hline
  \cellcolor{gray!25}\textbf{ad} stores the information about all the advertisements.
matric and time created for each advertisement are used for the primary key because a user can add more than one advertisement but the time created for each ad posted by a single student is unique. \\
  \begin{lstlisting}
CREATE TABLE ad(
	title VARCHAR(64) NOT NULL,
	category VARCHAR(32) CHECK (category='Books' OR 
		category='Furniture' OR category='Clothes'
		 OR category='Electronics' OR category='Kitchenware'
		  OR category='Stationary' OR category='Others' ),
	condition VARCHAR(32) CHECK (condition='As new' OR
		condition='Good' OR condition='Worn out' OR
		condition='Defect'),
	description VARCHAR(512),
	years_of_use VARCHAR(32),
	request VARCHAR(5) CHECK (request='Buy' OR
		request='Sell' OR request='Trade'),
	time_created DATE NOT NULL,
	price INT,
	student VARCHAR(12) NOT NULL,
	PRIMARY KEY (student, time_created),
	FOREIGN KEY (student) REFERENCES student(matric));
	\end{lstlisting} \\
\hline
\end{tabular}

\begin{tabular}{|p{1\textwidth}|}
  \hline
  \cellcolor{gray!25}\textbf{message} stores the details of all the messages between students.
\textbf{(sender, receiver, message\_time)} is the primary key of \textbf{message}. \\
  \begin{lstlisting}
CREATE TABLE message(
	subject VARCHAR(128),
	body VARCHAR(512),
	sender VARCHAR(12) NOT NULL,
	receiver VARCHAR(12) NOT NULL,
	message_time DATE NOT NULL,
	PRIMARY KEY (sender, receiver, message_time),
	FOREIGN KEY (sender) REFERENCES student(matric),
	FOREIGN KEY (receiver) REFERENCES student(matric));
		\end{lstlisting} \\ 
	\hline
\end{tabular}

\section{Web interface} 
2 or 3 representative screen dumps of the web interface 


\end{document}
